﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ProgNetUtils;

namespace ProgNetExtensions.DataProviderTools
{
    public class DataProviderCache
    {
        private static Dictionary<string, DataProvider> _Providers = new Dictionary<string, DataProvider>();
        private static Dictionary<string, List<string>> _ProvidersByTables = new Dictionary<string,List<string>>();

        private static Dictionary<string, Dictionary<string, DataTable>> ProvidersData = new Dictionary<string,Dictionary<string,DataTable>>();
        public static string ConnectionString { get; set; }
        private static DataProviderCache _Instance;

        public static DataProviderCache Instance
        {
            get
            {
                if (_Instance == null)
                    _Instance = new DataProviderCache();
                return _Instance;
            }
        }

        private SqlDataAdapter Adapter;
        
        public void RegisterProvider(DataProvider provider)
        {
            _Providers.Add(provider.Name, provider);
            if (!provider.IsStatic && provider.Tables != null)
            {
                foreach (string table in provider.Tables)
                {
                    List<string> list = null;
                    if (_ProvidersByTables.ContainsKey(table))
                        list = _ProvidersByTables[table];
                    else
                    {
                        list = new List<string>();
                        _ProvidersByTables.Add(table, list);
                    }
                    if (!list.Contains(provider.Name))
                    {
                        list.Add(provider.Name);
                    }
                }
            }
        }
        
        public DataProvider getProvider(string name)
        {
            return _Providers[name].Clone() as DataProvider;
        }
        
        
        private DataProviderCache()
        {
            Adapter = new SqlDataAdapter("", ConnectionString);
        }

        public void ClearCache(DataProvider provider)
        {
            ClearCache(provider.Name);
        }
        public void ClearCache(string provider)
        {
            if (ProvidersData.ContainsKey(provider))
            {
                ProvidersData.Remove(provider);
            }
        }

        public void ClearCacheByTable(string table)
        {
            if (_ProvidersByTables.ContainsKey(table))
            {
                List<string> list = _ProvidersByTables[table];
                foreach (string providerName in list)
                {
                    ClearCache(providerName);
                }
            }
        }
        public void ClearCacheByTable(DataSet ds)
        {
            foreach (DataTable dt in ds.Tables)
            {
                ClearCacheByTable(dt.TableName);
            }
        }

        private DataTable getRecords(DataProviderSearchConditions conditions, DataProvider provider, bool lookInCache)
        {
            Adapter.SelectCommand.CommandText = getSelectString(conditions, provider);

            string hashCode = Adapter.SelectCommand.CommandText;
            if (conditions != null)
                hashCode += conditions.PackSize.AsString().PutBetweenParenthesis() + conditions.StartRecordNumber.AsString().PutBetweenParenthesis();

            Dictionary<string, DataTable> dic = null;
            if (ProvidersData.ContainsKey(provider.Name))
                dic = ProvidersData[provider.Name];
            else
            {
                dic = new Dictionary<string, DataTable>();
                ProvidersData.Add(provider.Name, dic);
            }

            if (dic.ContainsKey(hashCode) && lookInCache)
                return dic[hashCode];
            else
            {
                DataTable dt = new DataTable();
                foreach (DataProviderColumn col in provider.Columns)
                    dt.Columns.Add(col.ColumnName);

                if (conditions != null && lookInCache)
                {
                    string hashCodeFull = getSelectString(null, provider);
                    if (dic.ContainsKey(hashCodeFull))
                    {
                        dt = dic[hashCodeFull].SelectToTable(getWhereString(conditions, provider), conditions.StartRecordNumber, conditions.PackSize);
                        if (!dic.ContainsKey(hashCode))
                            dic.Add(hashCode, dt);
                        else
                            dic[hashCode] = dt;
                        return dt;
                    }
                }
                

                if (conditions != null)
                    Adapter.Fill(conditions.StartRecordNumber, conditions.PackSize, dt);
                else
                    Adapter.Fill(dt);
                if (!dic.ContainsKey(hashCode))
                    dic.Add(hashCode, dt);
                else
                    dic[hashCode] = dt;

                return dt;
            }

        }
        
        private string getWhereString(DataProviderSearchConditions conditions, DataProvider provider)
        {
            string where = "";
            if (conditions != null && conditions.ColumnSearchOptions != null && conditions.ColumnSearchOptions.Length > 0)
            {
                foreach (StringKeyValuePair pair in conditions.ColumnSearchOptions)
                {
                    if (pair != null)
                    {
                        DataProviderColumn col = provider.Columns[pair.Key];
                        where = where.AppendAnd();
                        if (!col.CaseSensitiveSearch)
                            where += "UPPER(" + pair.Key + ")";
                        else
                            where += pair.Key;

                        string val = pair.Value.AsString();
                        if (!col.CaseSensitiveSearch)
                            val = val.ToUpper();

                        switch (col.FilterType)
                        {
                            case DataProviderColumnFilterType.BeginsWith:
                                where += " LIKE " + (val.EscapeSql(false) + "%").PutBetweenSingleQuotes(false);
                                break;
                            case DataProviderColumnFilterType.Bigger:
                                where += " > " + val.EscapeSql();
                                break;
                            case DataProviderColumnFilterType.BiggerOrEqual:
                                where += " >= " + val.EscapeSql();
                                break;
                            case DataProviderColumnFilterType.Contains:
                                where += " LIKE " + ("%" + val.EscapeSql(false) + "%").PutBetweenSingleQuotes(false);
                                break;
                            case DataProviderColumnFilterType.Equals:
                                where += " = " + val.EscapeSql();
                                break;
                            case DataProviderColumnFilterType.Smaller:
                                where += " < " + val.EscapeSql();
                                break;
                            case DataProviderColumnFilterType.SmallerOrEqual:
                                where += " <= " + val.EscapeSql();
                                break;
                        }
                    }

                }
            }
            return where;
        }
        private string getSelectString(DataProviderSearchConditions conditions, DataProvider provider)
        {
            string columns = "";
            string tables = "";
            string where = "";
            foreach (DataProviderColumn col in provider.Columns)
            {
                columns = columns.AppendSpaceComa();
                if (col.Expression.IsNotEmpty())
                    columns += col.Expression.PutBetweenParenthesis() + " AS ";
                columns += col.ColumnName;
            }
            foreach (string str in provider.Tables)
            {
                tables = tables.AppendSpaceComa();
                tables += str;
            }
            where = provider.Conditions;
            string tmp = getWhereString(conditions, provider);
            if (tmp.IsNotEmpty())
                where = where.AppendAnd() + tmp;

            if (where.IsNotEmpty())
                where = " WHERE " + where;

            string order = "";
            if (provider.OrderColumns != null)
            {
                foreach (string str in provider.OrderColumns)
                {
                    order = order.AppendSpaceComa();
                    order += str;
                }
                if (order.IsNotEmpty())
                    order = " ORDER BY " + order;
            }

            return "SELECT " + columns + " FROM " + tables + where + order;
        }
        private string getCountString(DataProvider provider)
        {
            string str = "SELECT COUNT(*) FROM ";
            string tables = "";
            foreach (string tab in provider.Tables)
            {
                tables = tables.AppendSpaceComa();
                tables += tab;
            }
            string where = provider.Conditions;
            if (where.IsNotEmpty())
                where = " WHERE " + where;

            return str + tables + where;
        }

        private int countRecords(DataProvider provider)
        {
            Adapter.SelectCommand.CommandText = getCountString(provider);
            DataTable dt = new DataTable();
            Adapter.Fill(dt);
            return dt.Rows[0][0].AsInt();
        }
    }
}
